 /****** Object:  Stored Procedure ResponsiblesGetList    Script Date: Wednesday, November 21, 2007 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spResponsiblesGetListByConPoint]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spResponsiblesGetListByConPoint]
GO

	
CREATE PROCEDURE spResponsiblesGetListByConPoint	
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output,
	@ConPointID int	
AS


SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempResponsibles (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ResponsibleID int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempResponsibles ([ResponsibleID]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 
	
SET @sql = @sql + ' [Responsibles].[ResponsibleID] FROM [Responsibles] '
				+ ' WHERE ( ' + CAST(@ConPointID as nvarchar) +  ' = -1 OR ' + CAST(@ConPointID as nvarchar) +  '=ConPointID) '
			    + ' ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [Responsibles]

SELECT	
	[Responsibles].[ResponsibleID],
	[Name],
	[ConpointID],
	(SELECT Description FROM ConPoints WHERE ConPoints.[ConpointID] = [Responsibles].[ConpointID]) AS Conpoint	
FROM
	#TempResponsibles AS tblTemp JOIN [Responsibles] ON
	tblTemp.ResponsibleID = [Responsibles].ResponsibleID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempResponsibles

/*

SELECT
	DISTINCT 
	[Responsibles].[ResponsibleID],
	[Name],
	[ConpointID],
	(SELECT Description FROM ConPoints WHERE ConPoints.[ConpointID] = [Responsibles].[ConpointID]) AS Conpoint
FROM
	[Responsibles]
WHERE (@ConPointID = -1 OR ConPointID=@ConPointID)
ORDER BY [Name]

*/
GO

	
